<?php
include 'inc/conn.php';

session_start();
extract($_POST);
extract($_GET);
unset($_POST,$_GET);

if(!isset($dept))
{
	if(empty($_SESSION['dept'])){
		$dept='TE';  // default is TE Daily report
	}else{
		$dept=$_SESSION['dept'];
	}
}

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('PRC');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';

// 将富文本编辑器里面的内容过滤
function html_to_text($string){
    $string = trim($string);  
    $string = str_replace("<p>", "", $string);
    $string = str_replace("<br>", chr(10), $string);
    $string = str_replace("<br />", chr(10), $string);
    $string = str_replace("</p>", chr(10), $string);
	$string = str_replace("&amp;", "&", $string);
	$string = str_replace("&nbsp;", " ", $string);
	$string = str_replace("&gt;", ">", $string);
	$string = str_replace("&quot;", "\"", $string);
	$string = str_replace("&#039;", "'", $string);
	if(!strstr($string, '<img')){
		$string = strip_tags($string);
	}
	return trim($string.PHP_EOL);
}

// 检查是否有图像的内容，有的话则攫取路径
function hasImg($string)
{
		if(strstr($string, '<img')){
			preg_match_all('/<img.*?src="(.*?)".*?>/is', $string, $result);
			if(strlen($result[1][0])<5){
				return "";
			}else{
				return str_replace("..", "", $result[1][0]);
			}
		}else{
			return "";
		}
}

// 获取当前的周别
function get_weeks_num(){
	$strTime = date("Y-m-d");
	$intWeek = ceil(((strtotime($strTime) - strtotime(date("Y")."-01-01 00:00:00")))/(7*86400));
	return $intWeek;
}

// 设置边框的数组
$styleThinBlackBorderOutline = array(
        'borders' => array(
            'allborders' => array( //设置全部边框
                'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
				),
        ),
);

// 定义要创建的表格名称
$Dept = Array("EE", "TE", "PE", "RT");

// 定义要写入数据的开始行
$rEE=5;
$rTE=5;
$rPE=5;
$rRT=5;

// 创建PHPExcel对象
$objPHPExcel = new PHPExcel();

// 设置文档属性
$objPHPExcel->getProperties()->setCreator("TE")
							 ->setLastModifiedBy("TE")
							 ->setTitle("TE Daily Report")
							 ->setSubject("TE Daily Report")
							 ->setDescription("TE Daily Report, generated using TE KS.")
							 ->setKeywords("TE, http://10.100.160.166")
							 ->setCategory("TE");

for($i=0;$i<1;$i++){
	// Add common Title
	$objPHPExcel->setActiveSheetIndex($i)
				->setCellValue('A2', 'Date') // 填写单元格内容
				->setCellValue('B2', 'Dept.') // 填写单元格内容
				->setCellValue('C2', 'Jobs')
				->setCellValue('D2', 'Owner')
				->setCellValue('E2', 'Issue day')
				->setCellValue('F2', 'Update day')
				->setCellValue('G2', 'Status')->getStyle("A2:G2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  // 设置单元格对齐
	
	$objPHPExcel->getActiveSheet()->getStyle("A2:G2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('538dd5');
	$objPHPExcel->getActiveSheet()->getStyle("A2:G2")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);   // 设置字体颜色
	
	$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(10);  // 设置第一行的行高
	$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(30);  // 设置第二行的行高
	$objPHPExcel->getActiveSheet()->getStyle("A2:G2")->applyFromArray($styleThinBlackBorderOutline);
	// 设置单元格自动换行
	$objPHPExcel->getActiveSheet()->getStyle("A")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("B")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle("C")->getAlignment()->setWrapText(true);
	$objPHPExcel->getActiveSheet()->getStyle("D")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
	$objPHPExcel->getActiveSheet()->getStyle("E")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
	$objPHPExcel->getActiveSheet()->getStyle("F")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
	$objPHPExcel->getActiveSheet()->getStyle("G")->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

	$objPHPExcel->getActiveSheet()->getColumnDimension('A') -> setWidth(11);;   // 设置列的宽度
	$objPHPExcel->getActiveSheet()->getColumnDimension('B') -> setWidth(6); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('C') -> setWidth(100); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('D') -> setWidth(9); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('E') -> setWidth(11); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('F') -> setWidth(11); 
	$objPHPExcel->getActiveSheet()->getColumnDimension('G') -> setWidth(8); 
	$objPHPExcel->getActiveSheet()->setTitle("TE");   // 设置表格名称
} 

	$sheet=0;
	$r=3;
	
	//进入数据库查询周报的全部内容
	$db = new mysql();
	$sql = "select * from daily_table where UID in (select ID from users where Department='${dept}') and (`add_date`>= '".date("Y-m-d")."' or `update_date`>='".date("Y-m-d")."') order by UNAME";
	$db->query($sql);
	$num = $db->db_num_rows();
	if ($num == 0){
		//echo "没有今日的日报内容!";
		$num=1;  // to create a null sheet
	}

	for($i=0; $i<$num; $i++){
		$row = $db->fetch_assoc();
		
		$objPHPExcel->setActiveSheetIndex($sheet)
			->setCellValue("C${r}", html_to_text(htmlspecialchars_decode($row['daily_content'])))
			->setCellValue("D${r}", html_to_text(htmlspecialchars_decode($row['UNAME'])))
			->setCellValue("E${r}", substr(html_to_text(htmlspecialchars_decode($row['add_date'])),0,10))
			->setCellValue("F${r}", substr(html_to_text(htmlspecialchars_decode($row['update_date'])),0,10))
			->setCellValue("G${r}", html_to_text(htmlspecialchars_decode($row['status'])));
			$objPHPExcel->getActiveSheet()->getStyle("C")->getAlignment()->setWrapText(true);
			$objPHPExcel->getActiveSheet()->getStyle("A${r}:G${r}")->applyFromArray($styleThinBlackBorderOutline);

			if(!$row['update_date']) {
				$objPHPExcel->getActiveSheet()->setCellValue("F${r}", "N/A");
			}

			if($row['status']=="Open") {
				$objPHPExcel->getActiveSheet()->getStyle("G${r}")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
			}
			if($row['status']=="Tracking") {
				$objPHPExcel->getActiveSheet()->getStyle("G${r}")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
			}

			$tmpStr=hasImg(htmlspecialchars_decode($row['daily_content']));
			if(strlen($tmpStr)>=5){
				$img=new PHPExcel_Worksheet_Drawing();
				$img->setPath('D:/WebSrv/htdocs'.$tmpStr);//写入图片路径
				$img->setHeight(200);//写入图片高度
				$img->setWidth(380);//写入图片宽度
				$img->setOffsetX(1);//写入图片在指定格中的X坐标值
				$img->setOffsetY(1);//写入图片在指定格中的Y坐标值
				$img->setRotation(0);//设置旋转角度
				$img->getShadow()->setVisible(false);
				$img->getShadow()->setDirection(0);
				$img->setCoordinates("C${r}");//设置图片所在表格位置
				$img->setWorksheet($objPHPExcel->getActiveSheet());//把图片写到当前的表格中
				$objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight(200);
			}
			$r++;
	}
	$mergRow=$r-1;
	
	$objPHPExcel->getActiveSheet()->mergeCells("A3:A${mergRow}")->setCellValue('A3', date("Y-m-d"));
	$objPHPExcel->getActiveSheet()->mergeCells("B3:B${mergRow}")->setCellValue('B3', ${dept})->getStyle("B3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);;

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Daily_Report-'.${dept}.'-'.date("md").'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();
$objWriter->save('php://output');
exit;
?>